** As several datasets used in the analysis are proprietary and cannot be shared, 
** this code is only given to help reserachers to understand the set up of our 
** databases and main variables.


*********************************************************************************
* This file produces a tidy version of the dataset that is later used for 
* regressions carried in Table 12 looking at the pre Covid impact of payment
* default on the likelihood of bankruptcy 
* in the paper Activity Shocks and Corporate Liquidity: the Role of Trade Credit.
*
* Script: do/Prepare_data_for_bankruptcy_regressions.do
* Authors:	
*			Anne 	 Duquerroy  [anne.duquerroy@banque-france.fr]
*			Benjamin Bureau [benjamin.bureau@banque-france.fr]
*			Frédéric Vinas  [frederic.vinas@banque-france.fr]
* Date: 	Jan. 19, 2024
*********************************************************************************
		
		* input:
		*   ../$datapath/defaillances_2017_22
		*   ../$datapath/cipe_2017_2022
		*   ../$mypath2/reg_cie
		*   ../$datapath/fiben_2016_2022 
		*	../$datapath/altman_z_score_naf_cotation
		*   ../$datapath/douteux
		
		* output:
		* ../$mypath2/siren_ipe_monthly
		* ../$mypath2/siren_delai_paiement_yearly_lagged
		* ../$mypath2/regression_long_period
		
			
*********************************************************************************************
// Prepare monthly data on bankruptcy event
*********************************************************************************************		
			 
			***********************************************
			use $datapath/defaillances_2017_22, replace
			***********************************************
			rename *, lower
			sort date_evt
			
			** Redressement and Liquidation judicaires = bankruptcy events
			gen defaillance	=	inlist(type_evt,"RJ","LJ")
			gen month = (annee-2017)*12+month(date_evt)
			bys siren month : 	egen defaillance	=	max(defaillance_tp)
		
			duplicates drop siren month, force
			keep siren month defaillance rj lj
			
			label variable defaillance "Bankruptcy"

			save $mypath2/siren_defaillance_month, replace
		
			* List of firm ID appearing in the unbalanced panel
			use $mypath2/reg_cie, replace
			duplicates drop siren, force
			save $mypath2/liste_siren_papier_cie, replace		
		
*********************************************************************************************
// Prepare table on payment default events on longer time period 2017-2022
*********************************************************************************************
				
			* Firms in unbalanced sample
				use $mypath/liste_siren_papier_cie , replace
				keep siren naf naf4digit naf5digit
				rename siren D1 		
				
			* Add payment defaults events 
				merge 1:m D1 using $datapath/cipe_2017_2022
				keep if inlist(_merge,1,3)
				drop _merge
				rename D1 siren
				replace incident=0 if incident==.
				order siren datrej		
				gen month = (year_rej-2017)*12+month(datrej)
				
				*br siren datrej month incident
				keep siren datrej month incident motif montant*
				drop if siren==.
				drop if datrej==.
				
				duplicates drop siren month , force
			
			******************************************
			save $mypath2/siren_ipe_monthly, replace	
			******************************************
			
*******************************************************************************************************
// Prepare table on firm financial statement characteristics on longer time period 2017-2022 (FIBEN)
*******************************************************************************************************
	
				use $mypath/liste_siren_papier_cie , replace
				keep siren 	
				merge 1:m siren using $datapath/fiben_2016_2022 
				
				** -- Drop if no 12 month fiscal year length
				drop if D5!=12
				sort siren fyear

				** Firm size
				gen 	taille_lme_string ="..."			
				replace taille_lme_string ="mic" 	if inlist(taille_lme,1,8)
				replace taille_lme_string ="pme" 	if inlist(taille_lme,2,3,4)
				replace taille_lme_string ="eti" 	if inlist(taille_lme,5)
				replace taille_lme_string ="ge_" 	if inlist(taille_lme,6)			
				replace taille_lme_string ="sci" 	if taille_lme==7
	

				*****************  2. VARIABLES DEFINITION  ***************	
				
					** Drop observations with missing total assets or missing sales			
					drop if ee		== .
					drop if fl		== .
					
					foreach v in  fy fz _5S _5T yu gr hp hq ze zf xq dl aa cm dr yq yr  _1A ac _5U ds dt dm du eh ys yq yr dv dn vi cf cg cd ce {
					replace `v'=0 if `v'==.
					}
					
					foreach v in bv bx cf cd cd cg dw dx fl fs fu fw ys yz yy {
							replace `v'=0 if `v'==.
					}
			
					** Financing 
					
					* - Bond finance
					gen oblig 	= ds + dt + dm
					* - Bank loans
					gen empbanc = du - eh 
					* - Overdrafts
					gen cbc 	= eh + ys
					* - Leasing : engcb
					* - Bank debt
					gen banc 	= empbanc + cbc + engcb
					* - Other loans
					gen autemp2 = dv + dn + vi - VI2
					* - Total financial debt
					gen endfi2 	= oblig + banc + autemp2 + VI2
		
					** Total Assets
					tsset siren fyear
					gen ta_lag = L1.ee
					
					** Leverage 
					gen leverage = endfi2 / ta_lag
					
					** Staff costs
					gen chpers  = fy + fz + _5S - _5T + yu 
					
					** Dividend paid
					gen div_pay = ze + zf
					
					** Cash and short-term liquid assets			
					gen cash    =(cf+cd)
					gen cash_ta =(cf+cd)/ta_lag
					
					** Rents
					rename xq 	  charges_loc
		
					** Ratios of receivables and payables 
					
					* - Sales 
					gen cattc 		= fl+yy							
					gen ca_lag		= L1.cattc 
					* - Purchases
					gen achatsttc 	= fs+fu+fw+yz
					* - Payables in nber of days of purchases
					gen  delai_frnissr 	  = 360 * (dx-bv) / achatsttc 
					gen  dfac = (dx-bv) / achatsttc 
					lab var dfac "Payables over Purchases"
					
					* - Receivables in nber of days of sales
					gen  delai_clnt 	  = 360 * (bx+ys-dw) / cattc 	
					gen  ccca 	  = (bx+ys-dw) / cattc 
					lab var ccca  "Receivable sover Sales"
					
					* - Trade credit over sales
					gen cie_ca		=	(bx+ys-dw-dx+bv)  / cattc
					lab var cie_ca 		"Trade Credit over Sales"
		
		
					** Winsorization of ratios at 1%
						foreach v in cash_ta div_ta leverage cash_ta cie_ca delai_frnissr delai_clnt ccca dfac  {
						replace `v'=0 if `v'==.
						winsor `v' , gen(`v'w) p(0.01)
						drop `v'
						rename `v'w `v'
							}

			rename fyear year
			replace year = year +1
			**********************************************************				
			save $mypath2/siren_delai_paiement_yearly_lagged, replace	
			**********************************************************
			
			
			******************************************
			use $mypath\regression_dataset, replace
			******************************************
	
			tab year
			* 2017 -2022
				
**********************************************************
* Make firm x month panel 01/2017 - 07/2022 
**********************************************************
			
				use $mypath2/liste_siren_papier_cie , replace
				keep siren naf naf4digit naf5digit
				expand (67)
				bys siren: gen month=_n						
				xtset siren month
			
			* Add Payment default data
				merge 1:1 siren month using $mypath2/siren_ipe_monthly
				keep if inlist(_merge,1,3)
				drop _merge
			
				replace incident=0 if incident==.
				rename  incident ipe
				gen ipe_illiquidity = (inlist(motif,"75", "31", "20"))
				gen ipe_dispute 	= (inlist(motif,"70", "73", "74", "76", "90"))
		
				lab var ipe_illiquidity     "Liquidity default"
				lab var ipe_dispute   		"Dispute default"
				
			* Add bankruptcy events			
				merge 1:1 siren month using $mypath2/siren_defaillance_month						
				keep if inlist(_merge,1,3)
				drop _merge		
				
				replace defaillance=0 	if defaillance==.					
			
				gen dummy_lockdown = ( 38 < month & month < 42)
			
			* Add financial statement
				gen year	=  2017 + floor((month-1)/12)
				rename month i_month
				gen month	=  mod(i_month,12)
				replace month	=  12 if month==0
				merge m:1 siren year using $mypath2/siren_delai_paiement_yearly
				keep if inlist(_merge,1,3)
				drop _merge
				drop month
				rename i_month month 
				
***************************************************************************		
*  Prepare data for regressions
**************************************************************************	
		
		xtset siren month
		
		*** -- Define FE 
		
		* year#firm
		egen siren_year = group(siren fyear) 
		
		* ind#time
		egen ind_year  = group(naf4d fyear)
		egen ind_month = group(naf4d date)
	
		** -- CIE
		** -- Take the opposite i.e. Payables net of Receivables, then standardize
		replace cie_ca = - cie_ca 
		egen 	cie_sd   = sd(cie_ca)
		gen 	cie_std   = cie_ca / cie_sd
		drop 	cie_ca 
		rename 	cie_std  cie_ca
		lab var cie_ca "TC ratio over sales, standardized"
		
		gen 	cie_post  = cie_ca * post
		lab var cie_post  "TC ratio over sales, standardized X Post"
		
		** Size = log of total assets
		gen size = ln(ta_lag)
	
		** Standardize Covariates
		foreach var in size  cash_ta  leverage{
		egen `var'_sd   = sd(`var')
		gen `var'_std   = `var'/ `var'_sd
		drop `var'_sd
		drop `var'
		rename `var'_std `var'
		}
		
		** Covariates x post		
		gen size_post 		= size 		* post
		gen cash_post 		= cash_ta 	* post
		gen dette_post 		= leverage 	* post


		** Credit rating 
				rename credpai rating
				* Recode rating numerically from 0 to 12
				gen rating_num=0 if rating=="0"
				replace rating_num = 1 if rating == "3++"
				replace rating_num = 2 if rating == "3+"
				replace rating_num = 3 if rating == "3"
				replace rating_num = 4 if rating == "4+"
				replace rating_num = 5 if rating == "4"
				replace rating_num = 6 if rating == "5+"
				replace rating_num = 7 if rating == "5"
				replace rating_num = 8 if rating == "6"
				replace rating_num = 9 if rating == "7"
				replace rating_num = 10 if rating == "8"
				replace rating_num = 11 if rating == "9"
				replace rating_num = 12 if rating == "P"
				
				** Non investment grade and not already under bankruptcy
				gen  low_rated = (rating_num > 4 & rating_num !=12)
		
		
		** Define Payment Default variables 
		
		** -- At least one IPE over the past 3 months due to dispute
		** -- Dispute
		gen ipe_disp_over3m = L1.ipe_dispute +L2.ipe_dispute + L3.ipe_dispute 
		gen at_least_one_dispute_3m = (ipe_disp_over3m > 0)
		
		** -- At least one IPE over the past 3 months due to illiquidity
		** Illiquidity
		gen ipe_illiq_over3m = L1.ipe_illiq +L2.ipe_illiq + L3.ipe_illiq  
		gen at_least_one_illiq_3m = (ipe_illiq_over3m > 0)
		
		** Sample period = 3 years before Covid
		drop if year == 2017 & month < 4
		
		***********************************************
		save $mypath2\regression_long_period, replace
		***********************************************

		
